#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import os
import shutil
import re

def extract_words_from_sql(sql_file_path):
    """从SQL文件中提取单词列表"""
    words = []
    # 修改正则表达式，匹配 'spelling' 字段的值
    word_pattern = re.compile(r"\(\d+,\s*'([^']+)'")
    
    try:
        with open(sql_file_path, 'r', encoding='utf-8') as f:
            sql_content = f.read()

        # 查找所有INSERT INTO dcyx_words语句中的单词
        matches = word_pattern.findall(sql_content)
        
        # 提取单词并转换为小写
        for match in matches:
            word = match.strip().lower()
            if word and word not in words:
                words.append(word)
        words = words[3:]
        words.append('fish')
        # 打印格式  idx: word
#         print(words[3:])
#         for idx, word in enumerate(words):
#             print(f"{idx + 1}: {word}")
        return words
    except Exception as e:
        print(f"读取SQL文件时出错: {e}")
        return []

def copy_audio_files(source_file, output_dir, words):
    """复制音频文件并重命名"""
    if not os.path.exists(source_file):
        print(f"源文件不存在: {source_file}")
        return False
    
    # 确保输出目录存在
    os.makedirs(output_dir, exist_ok=True)
    
    # 复制并重命名文件
    count = 0
    for word in words:
        if count >= 100:  # 只处理100个单词
            break
            
        target_file = os.path.join(output_dir, f"{word}.mp3")
        try:
            shutil.copy2(source_file, target_file)
            print(f"已创建: {target_file}")
            count += 1
        except Exception as e:
            print(f"复制文件时出错 {word}.mp3: {e}")
    
    print(f"总共创建了 {count} 个音频文件")
    return True

def main():
    # 文件路径
    current_dir = os.path.dirname(os.path.abspath(__file__))
    project_root = os.path.abspath(os.path.join(current_dir, "../.."))
    
    sql_file_path = os.path.join(project_root, "sql/mysql/dcyx_mockdata_chatgpt_0624.sql")
    source_audio_path = os.path.join(current_dir, "audio/good.mp3")
    output_dir = os.path.join(current_dir, "audio/")
    
    print(f"SQL文件路径: {sql_file_path}")
    print(f"源音频文件: {source_audio_path}")
    print(f"输出目录: {output_dir}")
    
    # 提取单词
    words = extract_words_from_sql(sql_file_path)
    if not words:
        print("未从SQL文件中提取到单词")
        return
    
    print(f"从SQL文件中提取到 {len(words)} 个单词")
    
    # 复制音频文件
    copy_audio_files(source_audio_path, output_dir, words[:100])
    
    print("处理完成!")

if __name__ == "__main__":
    main()